package com.coderman.modelfly.utils;

import com.alibaba.fastjson.JSON;

import java.io.*;
import java.sql.*;
import java.util.*;

/**
 * Created by ${fanchunshuai} on 2017-4-7.
 *
 * @author 58
 * @version: V1.0
 * @Desc: JDBC简单工具类
 * @Copyright (c) 2017 58到家-版权所有
 */
public class JdbcUtils {

    private static Properties properties = new Properties();
    private static Connection connection = null;
    private static String dbName = "";


    private static void init(String configPath){
        //已经初始化过之后不用再初始化
        if(!properties.isEmpty() ){
            return;
        }
        //InputStream is= JdbcUtils.class.getClassLoader().getResourceAsStream(configPath+"\\jdbc.properties");
        InputStream is;
        try {
            is = new FileInputStream(new File(configPath+"\\jdbc.properties"));
            properties.load(is);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * 初始化db连接
     * @return
     * @throws Exception
     */
    public static Connection initDBConnection(String configPath)throws Exception {

        init(configPath);

        // 1. 准备获取连接的 4 个字符串: user, password, url, jdbcDriver
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url= properties.getProperty("url");
        String jdbcDriver= properties.getProperty("jdbcDriver");

        // 2. 加载驱动: Class.forName(driverClass)
        Class.forName(jdbcDriver);
        // 3.获取数据库连接
        connection = DriverManager.getConnection(url, user, password);
        return  connection;
    }


    /**
     * 执行单条语句
     * @param sql
     * @return
     * @throws Exception
     */
    public static boolean exeOneSql(String sql)throws Exception {
        if(connection == null){
            System.out.println("connection is null ,please init it....");
            return false;
        }
        //保存当前自动提交模式
        boolean autoCommit = connection.getAutoCommit();
        //关闭自动提交
        connection.setAutoCommit(false);
        try {
            connection.prepareStatement(sql).execute();
            connection.commit();
            connection.setAutoCommit(autoCommit);
            return true;

        }catch (Exception e){
            connection.rollback();
            return false;
        }

    }

    /**
     * 批量执行多条语句
     * @param sqlList
     * @return
     * @throws Exception
     */
    public static boolean exeBatchSql(List<String> sqlList) throws Exception {
        try {
            if(connection == null){
                System.out.println("connection is null ,please init it ...");
                return false;
            }
            //保存当前自动提交模式
            boolean autoCommit=connection.getAutoCommit();
            //关闭自动提交
            connection.setAutoCommit(false);
            Statement preparedStatement = connection.createStatement();

            for (String sql : sqlList){
                preparedStatement.addBatch(sql);
            }
            preparedStatement.executeBatch();
            connection.commit();
            connection.setAutoCommit(autoCommit);
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            connection.rollback();
            return false;
        }
    }

    /**
     * 查询数据库所有字段信息
     * @param querySql
     * @return
     * @throws Exception
     */
    public static Map<String,List<String>> exeColumnSearchSql(String querySql) throws Exception {
        try {
            if(connection == null){
                System.out.println("connection is null ,please init it ...");
                return null;
            }
            System.out.println(querySql);
            PreparedStatement preparedStatement = connection.prepareStatement(querySql);
            ResultSet resultSet = preparedStatement.executeQuery();
            ResultSetMetaData md = resultSet.getMetaData(); //得到结果集(rs)的结构信息，比如字段数、字段名等
            int columnCount = md.getColumnCount(); //返回此 ResultSet 对象中的列数

            Map<String,List<String>> rowData = new HashMap();

            while (resultSet.next()) {

                List<String> rowList =  rowData.get(resultSet.getObject(1));
                if(rowList == null){
                    rowList = new ArrayList<String>();
                    rowData.put(resultSet.getObject(1).toString(),rowList);
                }
                String rowInfo = resultSet.getObject(1).toString();
                for (int i = 2; i <= columnCount; i++) {
                    String s = resultSet.getObject(i).toString();
                    rowInfo = rowInfo+"-"+s;
                }
                rowList.add(rowInfo);
                rowData.put(resultSet.getObject(1).toString(),rowList);
            }
            return rowData;
        } catch (Exception e) {
            e.printStackTrace();
            connection.rollback();
            return null;
        }
    }

    /**
     * 查询数据库表信息
     * @param querySql
     * @return
     * @throws Exception
     */
    public static Map<String,String> exeDBSearchSql(String querySql) throws Exception {
        try {
            if(connection == null){
                System.out.println("connection is null ,please init it ...");
                return null;
            }
            Statement preparedStatement = connection.createStatement();
            ResultSet resultSet = preparedStatement.executeQuery(querySql);
            ResultSetMetaData md = resultSet.getMetaData(); //得到结果集(rs)的结构信息，比如字段数、字段名等
            int columnCount = md.getColumnCount(); //返回此 ResultSet 对象中的列数
            System.out.println("columnCount = "+columnCount);
            Map<String,String> rowData = new HashMap();

            while (resultSet.next()) {
                rowData.put(resultSet.getObject(1).toString(),resultSet.getObject(2).toString());
            }
            return rowData;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }


    /**
     * 根据sql查询key--value形式的map
     * @param querySql
     * sql eg: select keyColumn,valueColumn from table
     * @return
     */
    public static Map<String,String>  exeQuerySql(String querySql){
        Map<String,String> map = new HashMap<String,String>();
        Statement preparedStatement = null;
        try {
            if(connection == null){
                System.out.println("connection is null ,please init it ...");
                return null;
            }
            preparedStatement = connection.createStatement();
            ResultSet resultSet = preparedStatement.executeQuery(querySql);
            while (resultSet.next()) {
                map.put(resultSet.getObject(1)+"",resultSet.getObject(2)+"");
            }

            return map;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }finally {
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static String getDbName(){

        return properties.getProperty("dbName");

    }



    public static void main(String[] args) throws Exception {
        String sqlArray [] = new String[4];
        sqlArray[0] = "insert into urls (websitename,websiteurl) values ('test1234555555','asdfasdflansldfnalsdnflansdlf')";
        sqlArray[1] = "insert into urls (websitename,websiteurl) values ('test1234555555','asdfasdflansldfnalsdnflansdlf')";
        sqlArray[2] = "insert into urls (websitename,websiteurl) values ('test1234555555','asdfasdflansldfnalsdnflansdlf')";
        sqlArray[3] = "insert into urls (websitename,websiteurl) values ('test1234555555','asdfasdflansldfnalsdnflansdlf')";
        String sql1 = "SELECT TABLE_NAME,COLUMN_NAME,COLUMN_TYPE AS TYPE,COLUMN_COMMENT FROM information_schema.columns WHERE TABLE_SCHEMA = 'dbdj_authority_test'";
        String metaSql = "SELECT TABLE_NAME,COLUMN_NAME,COLUMN_TYPE AS TYPE,COLUMN_COMMENT FROM information_schema.columns WHERE TABLE_SCHEMA = '"+ JdbcUtils.getDbName()+"'";

        JdbcUtils.initDBConnection("E:\\tmp\\modelfly\\config");

        String sql2 = "select id,nick_name from csdn_user ";
        //Map<String,String> map = JdbcUtils.exeQuerySql(sql1);

        Map<String,List<String>> map2 = JdbcUtils.exeColumnSearchSql(metaSql);
        System.out.println(JSON.toJSONString(map2));


       /* //String sql = "delete from urls where id in (318657,318658,318659,318660,318661)";

        Map<String,List<String>> map = JdbcUtils.exeSearchSql(sql1);

        Iterator<Map.Entry<String,List<String>>> it =  map.entrySet().iterator();
        while (it.hasNext()){
            System.out.println(it.next().getValue().toString());
        }


        System.out.println("b = "+map.size());
        System.out.println("b = "+map.keySet().toString());*/
        //System.out.println("b = "+map.values().toString());

        //boolean b = JdbcUtils.exeBatchSql(sqlArray);
        //System.out.println("b = "+b);

        //String tableSql = "SELECT TABLE_NAME,TABLE_COMMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'db_csdn'";
        //Map<String,String> map = JdbcUtils.exeDBSearchSql(tableSql);
        //System.out.println(map.toString());
    }




}
